﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcrTM].[utl_tm_period_generate] 
	@YEARS int
AS
DECLARE
	@BEGIN_DT DATE
	,@END_DT DATE
	,@CURRENT_DT DATE
	,@CNT INT
	,@CURKEY INT
	,@MAXPDT DATE
BEGIN TRY	
	IF @YEARS IS NULL OR @YEARS < 1
	 BEGIN
		RAISERROR(N'Invalid Parameters passed. NULL or Zero years is not supported.',16,1)
		return
	 END
	
	-- CHECK FOR EXISTING RECORDS.
	SELECT @CURKEY = MAX([TIME_KEY]) FROM FLCRTM.[PERIOD]
	SELECT @MAXPDT = [PERIOD_DATE] FROM FLCRTM.[PERIOD] WHERE [TIME_KEY] = @CURKEY
	
	IF	@CURKEY IS NULL
		BEGIN
			-- GENERATE INITIAL PERIOD TABLE.
			SELECT @BEGIN_DT = '01/01/2009'
			SELECT @CURRENT_DT = @BEGIN_DT, @CNT = 1
			-- Adds the requested years to the system creation date + install date. 
			SELECT @YEARS  = DATEDIFF(yy,@BEGIN_DT,DATEADD(yy, @YEARS, GETDATE()))
			SELECT @END_DT = DATEADD(yy, @YEARS, @BEGIN_DT)
			SELECT @END_DT = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@END_DT)+1, 0))
		END
	ELSE
		BEGIN
			-- EXTEND PERIOD TABLE.
			SELECT @CURRENT_DT = @MAXPDT, @CNT = @CURKEY +1
			SELECT @END_DT = DATEADD(yy, @YEARS, @MAXPDT)
			SELECT @END_DT = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@END_DT)+1, 0))	
		END
	-- GENERATE THE DAY RECORDS FOR THE PERIOD TABLE.
	WHILE @CURRENT_DT <= @END_DT
		BEGIN
		  INSERT INTO FLCRTM.[PERIOD] ([TIME_KEY], [PERIOD_DATE])
		  VALUES (@CNT, @CURRENT_DT)

		  SELECT @CNT = @CNT + 1, @CURRENT_DT = DATEADD(DD, 1, @CURRENT_DT)	
		END
END TRY
BEGIN CATCH
	EXEC flcr.[utl_getDBError];
END CATCH